import os

import psycopg2
import pymysql

data_root_path = "../dataset"


def insert_all():
    with open(os.path.join(data_root_path, 'FAQ.txt'), 'r', encoding='utf-8') as f_data:
        lines = f_data.readlines()
        for idx, line in enumerate(lines):
            split = line.split("|")
            # fqa_id = split[0]
            answer = split[1].replace("\n", "")
            insert_fqa(idx, answer)

    with open(os.path.join(data_root_path, 'train.txt'), 'r', encoding='utf-8') as f_data:
        lines = f_data.readlines()
        for idx, line in enumerate(lines):
            split = line.split("|")
            fqa_id = split[0]
            question = split[1].replace("\n", "")
            insert_train(idx, fqa_id, question)


def insert_fqa(idx, answer):
    sql = "INSERT INTO qa_fqa(answer,device_id,major) VALUES ('%s',1,2)" % answer
    # connect_mysql(sql)
    connect_post_gres(sql)
    print("qa_fqa", idx)


def insert_train(idx, fqa_id, question):
    sql = "INSERT INTO qa_train(fqa_id, question) VALUES ('%s','%s')" % (fqa_id, question)
    connect_post_gres(sql)
    # connect_mysql(sql)
    print("qa_train", idx)


def connect_mysql(sql):
    connect = pymysql.Connect(
        host="192.168.8.23",
        port=3306,
        user="root",
        passwd="root",
        db="ld_qa",
        charset='utf8'
    )
    cursor = connect.cursor()
    cursor.execute(sql)
    li = cursor.fetchall()
    connect.commit()
    cursor.close()
    connect.close()
    return li


def connect_post_gres(sql):
    connect = psycopg2.connect(
        database="jjgj_db", user="postgres", password="123456", host="192.168.8.25", port="5432"
    )
    cursor = connect.cursor()
    cursor.execute(sql)
    connect.commit()
    cursor.close()
    connect.close()


if __name__ == '__main__':
    insert_all()
